﻿Imports System.Data.OleDb
Public Class TietKiemDAO
    Shared conn_str As String = My.Settings.ConnectionString
    Shared Function GetAll(ByVal MaUser As Integer) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select * from TietKiem join nganhang on nganhang.manganhang = TietKiem.manganhang " & _
            "join thunhap on thunhap.mathunhap = tietkiem.mathunhap " & _
            "where MaUser = ?"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.AddWithValue("@MaUser", MaUser)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function TongTien(ByVal MaUser As Integer) As Double
        Dim tb As Double
        Dim query As String = "select sum(sotien) from TietKiem " & _
        "where MaUser = ?"
        Try
            Using conn As New OleDbConnection(conn_str)
                Dim cmd As New OleDbCommand(query, conn)
                cmd.Parameters.AddWithValue("@MaUser", MaUser)
                conn.Open()
                Double.TryParse(cmd.ExecuteScalar().ToString(), tb)
                conn.Close()
            End Using
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return tb
    End Function
    Shared Function PhatSinhThu(ByVal MaUser As Integer) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select *,dateadd(mm,KyHan,NgayGui) NgayDaoHan from TietKiem where MaUser = ?"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.AddWithValue("@MaUser", MaUser)
            adapter.Fill(dt)
            If dt.Rows.Count > 0 Then
                Dim str As String = "SELECT count(*) FROM PhieuThu where ChiTiet LIKE ? and MaUserThu = ? and MONTH(Ngay) = MONTH(CURRENT_TIMESTAMP)"
                Dim conn As New OleDbConnection(conn_str)
                Dim cmd As New OleDbCommand(str, conn)
                cmd.Parameters.AddWithValue("@ChiTiet", "tietkiem")
                cmd.Parameters.AddWithValue("@MaUserThu", MaUser)
                conn.Open()
                Dim count As Integer = cmd.ExecuteScalar()
                conn.Close()
                If count = 0 Then
                    Dim tmp As DataRow
                    Dim dt_phieuthu As DataTable = DB_Provider.Read_schema("PhieuThu")
                    For Each dr As DataRow In dt.Rows
                        'Neu tu dong gia han thi tinh lai tung thang, neu khong thi khong tinh lai
                        If dr("TuDongGiaHan") Or (dr("TuDongGiaHan") = 0 And dr("NgayGui") <= Date.Now.ToShortDateString() And Date.Now.ToShortDateString() <= dr("NgayDaoHan")) Then
                            tmp = dt_phieuthu.NewRow()
                            tmp.Item("SoTien") = dr.Item("TienLaiHangThang")
                            tmp.Item("Ngay") = Date.Now.ToString("yyyy-MM-dd")
                            tmp.Item("MaUserThu") = MaUser
                            tmp.Item("MaUserLap") = MaUser
                            tmp.Item("MaNganHang") = dr.Item("MaNganHang")
                            tmp.Item("MaThuNhap") = dr.Item("MaThuNhap")
                            tmp.Item("ChiTiet") = "tietkiem"
                            dt_phieuthu.Rows.Add(tmp)
                            TaiKhoanNganHangDAO.CongTien(MaUser, dr.Item("MaNganHang"), dr.Item("TienLaiHangThang"))
                        End If
                    Next
                    DB_Provider.Write(dt_phieuthu, "PhieuThu")                                        
                End If
            End If
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
End Class
